SQL Server Roles

A role is a set of permissions. Functions help simplify route planning. For example, instead of assigning permissions to individual users, you can group permissions into roles and add users to those roles.

• First, create a role.
• Second, grant permissions to the role. 
• Third, add one or more users to the role.

SQL Server Role Type

SQL Server provides you with three main role types,

Server-level Roles – manage the permissions on SQL Server, like changing server configuration.
Database Level Roles – Manage permissions on databases such as creating tables and querying data.
Application level Role– Allow applications to run with their own, user-like permissions.
 

SQL Server provides two types for each role,

Fixed server roles are created roles provided by SQL Server. These functions have fixed licenses.
User-defined roles creates a role that you define to meet a specific security need.

Adding a user to a Role

First, create a new login called testLogin

CREATE LOGIN testLogin
WITH PASSWORD='UyxIv@12'

Now, switch the current database to MyCollegeDb and create a user for testLoginlogin

USE MyCollegeDb
GO

CREATE USER MyUser
FOR LOGIN testLogin;

Then, connect to the MyCollegeDb database using user MyUser . User MyUser  can view the MyCollegeDb database but cannot see any database objects.

After that, add the user MyUser to the db_datareader role.

Use MyCollegeDb
GO
ALTER ROLE db_datareader
ADD MEMBER MyUser 

db_datareader is a fixed database role. The db_datareader role allows all members to read data from all user tables and views in the database. Technically, it is equivalent to the following GRANT statement

GRANT SELECT 
ON DATABASE::MyCollegeDb
TO MyUser;

In this example, DATABASE is a class type that comes after :: indicating a separable that is a database. Following are the available class types,

• LOGIN
• DATABASE
• OBJECT
• ROLE
• SCHEMA
• USER

Finally, switch the connection to the user MyUserand select data from the dbo.Employeestable,

Also, Read: How to create user-defined role in SQL Server Database?